//
//  myData.m
//  Sqlite 数据库
//
//  Created by 董骎 on 14-5-12.
//  Copyright (c) 2014年 董骎. All rights reserved.
//

#import "myData.h"
#import "myObj.h"
@implementation myData
-(NSString *)getDocPath
{
    NSArray *paths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myPath=[paths objectAtIndex:0];
    NSString *path=[myPath stringByAppendingPathComponent:@"dish.sql"];
    return path;
}
-(BOOL)initData
{
    NSString *pathForData=[self getDocPath];
    /*
     1,sqlite3_open(数据库文件路径，数据库);打开数据库
     2,sqlite3_close(数据库);关闭数据库
     3,SQLITE_OK(可操作状态 OK)
     */
    if (sqlite3_open([pathForData UTF8String],&mySql)!=SQLITE_OK) {
        sqlite3_close(mySql);
        return NO;
    }
    return YES;
}
//判断当前库是否可以打开
-(BOOL)creatDataDishTable
{
    sqlite3_stmt *stmt;
    //声明结构语句 保存结果对象
    NSString *creatSQL=@"create table if not exists dishName (name text,bigImg text,tip text,introduction text,smallImg text)";
    //create table if not exists 表名(字段1 类型,字段2 类型,字段3 类型)
    //创建SQL语句
    if (sqlite3_prepare_v2(mySql, [creatSQL UTF8String], -1, &stmt, nil)!=SQLITE_OK) {
        return NO;
    }
    //准备当前数据库
    sqlite3_step(stmt);
    //执行结构语句命令
    sqlite3_finalize(stmt);
    //释放结构语句命令
    return YES;
}
-(BOOL)creatDataMainMaterial {
    sqlite3_stmt *stmt;
    //声明结构语句 保存结果对象
    NSString *creatSQL=@"create table if not exists mainMaterial (name text,material text,quantity text)";
    //create table if not exists 表名(字段1 类型,字段2 类型,字段3 类型)
    //创建SQL语句
    if (sqlite3_prepare_v2(mySql, [creatSQL UTF8String], -1, &stmt, nil)!=SQLITE_OK) {
        return NO;
    }
    //准备当前数据库
    sqlite3_step(stmt);
    //执行结构语句命令
    sqlite3_finalize(stmt);
    //释放结构语句命令
    return YES;
}
-(BOOL)creatDataHowToDoTable {
    sqlite3_stmt *stmt;
    //声明结构语句 保存结果对象
    NSString *creatSQL=@"create table if not exists howToDo (name text,imgUrl text,info text)";
    //create table if not exists 表名(字段1 类型,字段2 类型,字段3 类型)
    //创建SQL语句
    if (sqlite3_prepare_v2(mySql, [creatSQL UTF8String], -1, &stmt, nil)!=SQLITE_OK) {
        return NO;
    }
    //准备当前数据库
    sqlite3_step(stmt);
    //执行结构语句命令
    sqlite3_finalize(stmt);
    //释放结构语句命令
    return YES;
}
-(BOOL)creatDataAuxiliaryMaterial {
    sqlite3_stmt *stmt;
    //声明结构语句 保存结果对象
    NSString *creatSQL=@"create table if not exists auxiliaryMaterial (name text,material text,quantity text)";
    //create table if not exists 表名(字段1 类型,字段2 类型,字段3 类型)
    //创建SQL语句
    if (sqlite3_prepare_v2(mySql, [creatSQL UTF8String], -1, &stmt, nil)!=SQLITE_OK) {
        return NO;
    }
    //准备当前数据库
    sqlite3_step(stmt);
    //执行结构语句命令
    sqlite3_finalize(stmt);
    //释放结构语句命令
    return YES;
}

-(BOOL)insertData:(NSString *)name title:(NSString *)title  info:(NSString *)info tableName:(NSString *)tableName introduction:(NSString *)introduction smallImg:(NSString *)smallImg {
    sqlite3_stmt *myStmt;
    NSString *insertSQL;
    if ([tableName isEqualToString:@"dishName"]) {
        insertSQL = [NSString stringWithFormat:@"insert into dishName (name,bigImg,tip,introduction,smallImg) values (?,?,?,?,?)"];
        if (sqlite3_prepare_v2(mySql, [insertSQL UTF8String], -1, &myStmt, nil)!=SQLITE_OK) {
            return NO;
        }
        sqlite3_bind_text(myStmt, 1, [name UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(myStmt, 2, [title UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(myStmt, 3, [info UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(myStmt, 4, [introduction UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(myStmt, 5, [smallImg UTF8String], -1, SQLITE_TRANSIENT);

    } else if ([tableName isEqualToString:@"howToDo"]) {
        insertSQL = [NSString stringWithFormat:@"insert into %@ (name,imgUrl,info) values (?,?,?)",tableName];
        if (sqlite3_prepare_v2(mySql, [insertSQL UTF8String], -1, &myStmt, nil)!=SQLITE_OK) {
            return NO;
        }
        sqlite3_bind_text(myStmt, 1, [name UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(myStmt, 2, [title UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(myStmt, 3, [info UTF8String], -1, SQLITE_TRANSIENT);
    } else {
        insertSQL = [NSString stringWithFormat:@"insert into %@ (name,material,quantity) values (?,?,?)",tableName];
        if (sqlite3_prepare_v2(mySql, [insertSQL UTF8String], -1, &myStmt, nil)!=SQLITE_OK) {
            return NO;
        }
        sqlite3_bind_text(myStmt, 1, [name UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(myStmt, 2, [title UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(myStmt, 3, [info UTF8String], -1, SQLITE_TRANSIENT);
    }
    sqlite3_step(myStmt);
    sqlite3_finalize(myStmt);
    return YES;
}
-(BOOL)deleteTestList:(NSString *)name tableName:(NSString *)tableName
{
    sqlite3_stmt *statement;
    NSString *sql=[NSString stringWithFormat:@"delete from %@ where name = ?",tableName];
    if (sqlite3_prepare_v2(mySql, [sql UTF8String], -1, &statement, nil)!=SQLITE_OK) {
        return NO;
    }
//    sqlite3_bind_int(statement, 1, rowid);
    sqlite3_bind_text(statement, 1, [name UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_step(statement);
    sqlite3_finalize(statement);
    return YES;
}
-(BOOL)updataData:(NSString *)name PassWord:(int)pass Rowid:(int)rowid
{
    NSString *upData=@"update GroupTable set Name = ? ,PassWord = ? where rowid = ?";
    sqlite3_stmt *updataStmt;
    if (sqlite3_prepare_v2(mySql, [upData UTF8String], -1, &updataStmt, nil)!=SQLITE_OK) {
        return NO;
    }
    sqlite3_bind_text(updataStmt, 1, [name UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(updataStmt, 2, pass);
    sqlite3_bind_int(updataStmt, 3, rowid);
    sqlite3_step(updataStmt);
    sqlite3_finalize(updataStmt);
    return YES;
}
-(NSMutableArray *)selectData {
    sqlite3_stmt *selectStmt;
    NSString *selectSQL = @"select * from dishName";
    if (sqlite3_prepare_v2(mySql, [selectSQL UTF8String], -1, &selectStmt,nil)!=SQLITE_OK) {
        return NO;
    }
    NSMutableArray *array=[NSMutableArray arrayWithCapacity:0];
    while (sqlite3_step(selectStmt)==SQLITE_ROW) {
        myObj *testMyObj=[[myObj alloc]init];
        char *dbName=(char *)(sqlite3_column_text(selectStmt, 0));
        NSString *name=[NSString stringWithCString:dbName encoding:NSUTF8StringEncoding];
        testMyObj.nameString=name;
        
        char *dpassId=(char *)(sqlite3_column_text(selectStmt, 4));
        NSString *passId=[NSString stringWithCString:dpassId encoding:NSUTF8StringEncoding];
        testMyObj.smallImg=passId;
        
        char *dintroduction = (char *)(sqlite3_column_text(selectStmt, 3));
        NSString *introduction = [NSString stringWithCString:dintroduction encoding:NSUTF8StringEncoding];
        testMyObj.introduction = introduction;
        
        char *dtips = (char *)(sqlite3_column_text(selectStmt, 2));
        NSString *tips = [NSString stringWithCString:dtips encoding:NSUTF8StringEncoding];
        testMyObj.tips = tips;
        
        char *dbigImg = (char *)(sqlite3_column_text(selectStmt, 1));
        NSString *bigImg = [NSString stringWithCString:dbigImg encoding:NSUTF8StringEncoding];
        testMyObj.passNum = bigImg;
        
        [array addObject:testMyObj];
    }
    sqlite3_finalize(selectStmt);
    return array;
}
-(NSMutableArray *)selectHowToDo:(NSString *)tableName :(NSString *)keyWord {
    sqlite3_stmt *selectStmt;
    NSString *selectSQL = [NSString stringWithFormat:@"select * from %@ where name = '%@'",tableName,keyWord];
    if (sqlite3_prepare_v2(mySql, [selectSQL UTF8String], -1, &selectStmt,nil)!=SQLITE_OK) {
        return NO;
    }
    NSMutableArray *array=[NSMutableArray arrayWithCapacity:0];
    while (sqlite3_step(selectStmt)==SQLITE_ROW) {
        myObj *testMyObj=[[myObj alloc]init];
        char *dbName=(char *)(sqlite3_column_text(selectStmt, 0));
        NSString *name=[NSString stringWithCString:dbName encoding:NSUTF8StringEncoding];
        testMyObj.nameString=name;
        
        char *dtips = (char *)(sqlite3_column_text(selectStmt, 2));
        NSString *tips = [NSString stringWithCString:dtips encoding:NSUTF8StringEncoding];
        testMyObj.tips = tips;
        
        char *dbigImg = (char *)(sqlite3_column_text(selectStmt, 1));
        NSString *bigImg = [NSString stringWithCString:dbigImg encoding:NSUTF8StringEncoding];
        testMyObj.passNum = bigImg;
        
        [array addObject:testMyObj];
    }
    sqlite3_finalize(selectStmt);
    return array;

    
}
@end
